<?php
class Hotel
{
    /**
     * @var object Zend DB Adapter
     */
    private $_db;
    
    public function __construct()
    {
			$this->_db = Zend_Registry::get('db');
    }
    
    public function getHotelDetailsById($hotel_id)
    {
    	$this->_db->query( "set names 'utf8'" ) ;
    	$sql = $this->_db->fetchRow("
    		SELECT H.hotel_title, CNT.country_name, CIT.city_name, H.hotel_description, H.hotel_facility_en, H.hotel_facility_ru, H.contact_email, H.hotel_phone, H.hotel_fax, H.hotel_skype, H.no_rooms, I.filename
    		FROM hotels H
    		LEFT JOIN cities CIT ON CIT.city_id = H.hotel_city
    		LEFT JOIN countries CNT ON CNT.country_id = H.hotel_country
    		LEFT JOIN hotel_images I ON I.hotel_id = H.hotel_id
    		WHERE H.hotel_id = " . $hotel_id . " AND H.is_Enabled = 1");
    	
    	return $sql;
    }
    
    public function getHotelDetails($hotel_country=NULL)
    {
        $where='';
        if (!empty($hotel_country)){
            $where = " and hotel_country={$hotel_country} ";
        }
    	$this->_db->query( "set names 'utf8'" ) ;
    	$sql = $this->_db->fetchAll("
    		SELECT H.hotel_id, H.hotel_title, CNT.country_name, CIT.city_name, H.hotel_description, H.hotel_facility_en, H.hotel_facility_ru, H.contact_email, H.hotel_phone, H.hotel_fax, H.hotel_skype, H.no_rooms, I.filename
    		FROM hotels H
    		LEFT JOIN cities CIT ON CIT.city_id = H.hotel_city
    		LEFT JOIN countries CNT ON CNT.country_id = H.hotel_country
    		LEFT JOIN hotel_images I ON I.hotel_id = H.hotel_id
    		WHERE H.is_Enabled = 1 {$where} 
    		GROUP BY H.hotel_id");
    	
    	return $sql;
    }   
    
    public function getHotelImageByHotelId($hotel_id)
    {
    	$img_arr = $this->_db->fetchAll("
    		SELECT I.filename, H.hotel_title
    		FROM hotel_images I
    		LEFT JOIN hotels H ON H.hotel_id = I.hotel_id
    		WHERE H.hotel_id = " . $hotel_id . "    		
    		LIMIT 0, 4");
    	
    	return $img_arr;
    }
    
    public function getRoomTypeByCatId($room_cat_id) 
    {
    	$room_cat_arr = $this->_db->fetchAll("
    		SELECT room_type
    		FROM roomcategories
    		WHERE roomcat_id = " . $room_cat_id
    	);
    	
    	return $room_cat_arr;
    }
    
    public function getAmenitiesByHotelId($hotel_id)
    {
    	$amenity_arr = $this->_db->fetchAll("
    		SELECT amenity_type
    		FROM amenities A
    		LEFT JOIN hotel_amenities HA ON HA.amenity_id = A.amenity_id
    		WHERE HA.hotel_id = " . $hotel_id);
    	
    	return $amenity_arr;
    }
    
    public function getAmenitiesByAmenityId($amenity_id)
    {    
    	$amenity_arr = $this->_db->fetchAll("
    		SELECT amenity_type
    		FROM amenities A
    		LEFT JOIN hotel_amenities HA ON HA.amenity_id = A.amenity_id
    		WHERE A.amenity_id = " . $amenity_id);
    	
    	return $amenity_arr;
    }
     public function getHotelRates($hotel_id)
    {
    	$rates_arr = $this->_db->fetchAll(" select * from hotel_rates where hotel_id= " . $hotel_id." ");
// echo " select * from rates where  hotel_id= " . $hotel_id;exit;
    	return $rates_arr;
    }

    public function  getHotelRatesstructuredata($rate_id)
    {
      //  echo " select * from ratestructuredata where rateid in (select rate_id from hotel_rates where hotel_id=".$hotel_id.")";exit;
           	$rates_arr = $this->_db->fetchAll(" select * from ratestructuredata where rateid=".$rate_id."");
 //echo " select * from ratestructuredata where rateid in (select rate_id from hotel_rates where hotel_id=".$hotel_id.")";exit;
    	return $rates_arr;
    }

    public function getHotelDetailsByCountry($cid)
    {
    	$this->_db->query( "set names 'utf8'" ) ;
    	$sql = $this->_db->fetchAll("
    		SELECT H.hotel_title, CNT.country_name, CIT.city_name, H.hotel_description, H.hotel_facility_en, H.hotel_facility_ru, H.contact_email, H.hotel_phone, H.hotel_fax, H.hotel_skype, H.no_rooms, I.filename
    		FROM hotels H
    		LEFT JOIN cities CIT ON CIT.city_id = H.hotel_city
    		LEFT JOIN countries CNT ON CNT.country_id = H.hotel_country
    		LEFT JOIN hotel_images I ON I.hotel_id = H.hotel_id
    		WHERE H.hotel_country = " . $cid . " AND H.is_Enabled = 1");

    	return $sql;
    }
} 
?>